﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WaveMaster.Holidays.Club.Gateway;
using WaveMaster.Holidays.Club.Domain;
using System.Collections;
using MySql.Data.MySqlClient;
using WaveMaster.Holidays.Club.SQL;
using System.Data;

namespace WaveMaster.Holidays.Club.Service
{
    public class CheckInService : IService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        public CheckInService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
        }
        public CheckInService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public IList LoadAllCheckIn()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.GetAllBooking(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    CheckIn user = new CheckIn(rd);
                    MySqlCommand cmd2 = new MySqlCommand(PassangerSQL.GetAllPassangerByBookingID(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    cmd2.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = user.m_bookingID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    MySqlDataReader rd2 = cmd2.ExecuteReader();
                    while (rd2.Read())
                    {
                        Passenger p = new Passenger(rd2);
                        user.m_passengerList.Add(p);
                    }
                    rd2.Close();


                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public CheckIn GetCheckInByID(long id)
        {
            CheckIn result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.GetBookingByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new CheckIn(rd);
                    MySqlCommand cmd2 = new MySqlCommand(PassangerSQL.GetAllPassangerByBookingID(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    cmd2.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = result.m_bookingID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    MySqlDataReader rd2 = cmd2.ExecuteReader();
                    while (rd2.Read())
                    {
                        Passenger p = new Passenger(rd2);
                        result.m_passengerList.Add(p);
                    }
                    rd2.Close();

                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }
        public CheckIn GetCheckInByID(long id, TravelAgent travelAgent)
        {
            CheckIn result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.GetBookingByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new CheckIn(rd);
                    MySqlCommand cmd2 = new MySqlCommand(PassangerSQL.GetAllPassangerByBookingIDAndTravelAgentID(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    cmd2.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = result.m_bookingID;
                    cmd2.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    MySqlDataReader rd2 = cmd2.ExecuteReader();
                    while (rd2.Read())
                    {
                        Passenger p = new Passenger(rd2);
                        result.m_passengerList.Add(p);
                    }
                    rd2.Close();

                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public CheckIn GetCheckInHistoryByBookingID(long id)
        {
            CheckIn result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.GetBookingHistoryByBookingID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new CheckIn(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public Passenger GetPassengerHistoryByPassengerID(long id)
        {
            Passenger result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.GetPassengerHistoryByPassengerID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passangerid", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Passenger(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList SearchCheckInByCodeName(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchBookingByCodeName(p), m_dataBaseConectivity.InterfaceSqlConnection);
                //cmd.Parameters.Add("code", MySqlDbType.VarChar).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new CheckIn(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearchCheckInByDepartureDate(DateTime p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchBookingByDepartureDate(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_departuredate", MySqlDbType.Date).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new CheckIn(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearchCheckInByCodeNameDepartureDate(string s, DateTime p, CheckInStatus ch)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchBookingByCodeNameDepartureDateStatus(s), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_departuredate", MySqlDbType.Date).Value = p;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.String).Value = ch.ToString();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new CheckIn(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearchCheckInByCodeNameAndTravelAgent(string p, long travelAgentID, DateTime dt, CheckInStatus ch)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchBookingByCodeNameAndTravelAgentIdAndDatetimeStatus(p), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgentID;
                cmd.Parameters.Add("m_departuredate", MySqlDbType.Date).Value = dt;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.String).Value = ch.ToString();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new CheckIn(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearchCheckInByCodeNameDepartureDate(string s, DateTime p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchBookingByCodeNameDepartureDate(s), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_departuredate", MySqlDbType.Date).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new CheckIn(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearchCheckInByCodeNameTripDepartureDate(string s, DateTime p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchBookingByCodeNameTripDepartureDate(s), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_departuredate", MySqlDbType.Date).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new CheckIn(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList SearchCheckInByCodeNameAndTravelAgent(string p, long travelAgentID, DateTime dt)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchBookingByCodeNameAndTravelAgentIdAndDatetime(p), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgentID;
                cmd.Parameters.Add("m_departuredate", MySqlDbType.Date).Value = dt;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new CheckIn(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public void DeleteCheckIn(CheckIn booking)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.DeletePassangerByBookingID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = booking.m_bookingID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                cmd.CommandText = BookingSQL.DeleteBookingByID();
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = booking.m_bookingID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();

                //UPdate bookstock pluss
                cmd.Parameters.Clear();
                if (booking.m_ticketType == TicketType.OneWay)
                    cmd.CommandText = BookingSQL.UpdateAddOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.UpdateAddTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("addition", MySqlDbType.Decimal).Value = booking.m_bookNumber;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateCheckIn(CheckIn book, TravelAgent travelAgent)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();

                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerCountByTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                int countPassenger = Convert.ToInt32(cmd.ExecuteScalar());
                //UPdate bookstock pluss
                cmd.Parameters.Clear();
                cmd.CommandText = BookingSQL.UpdateAddOneWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                cmd.Parameters.Add("addition", MySqlDbType.Decimal).Value = countPassenger;
                cmd.ExecuteNonQuery();
                
                //Get Last Balance-02022013 no longer use
                cmd.Parameters.Clear();
                if (book.m_ticketType == TicketType.OneWay)
                    cmd.CommandText = BookingSQL.GetOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.GetTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = book.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("substract", MySqlDbType.Decimal).Value = book.m_bookNumber;
                book.m_lastBalance = Convert.ToDecimal(cmd.ExecuteScalar());

                //update booking
                cmd.Parameters.Clear();
                cmd.CommandText =BookingSQL.UpdateBooking();
                cmd.Parameters.Add("m_groupName", MySqlDbType.VarChar).Value = book.m_groupName;
                cmd.Parameters.Add("m_bookingCode", MySqlDbType.VarChar).Value = book.m_bookingCode;
                cmd.Parameters.Add("m_bookNumber", MySqlDbType.Decimal).Value = book.m_bookNumber;
                cmd.Parameters.Add("m_lastBalance", MySqlDbType.Decimal).Value = book.m_lastBalance;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = book.m_trip.m_tripID;
                cmd.Parameters.Add("m_returntrip", MySqlDbType.Int32).Value = book.m_returntrip == null ? 0 : book.m_returntrip.m_tripID;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = book.m_remark;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = book.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.VarChar).Value = book.m_bookingStatus == CheckInStatus.Boarding ? CheckInStatus.ReCeckIn : book.m_bookingStatus;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = book.m_ticketType;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = book.m_modfctnBy;
                cmd.Parameters.Add("m_bookingDate", MySqlDbType.Datetime).Value = book.m_bookingDate;
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = book.m_bookingID;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = book.m_departureDate;
                cmd.Parameters.Add("m_returnDate", MySqlDbType.Datetime).Value = book.m_returnDate;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                //CHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHECCCCCCKKKKKKKKKKKKKKKKKK


                //IList OldPassengerList = new ArrayList();
                //MySqlCommand cmd2 = new MySqlCommand(PassangerSQL.GetAllPassangerByBookingID(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                //cmd2.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = book.m_bookingID;
                //MySqlDataReader rd2 = cmd2.ExecuteReader();
                //while (rd2.Read())
                //{
                //    Passenger p = new Passenger(rd2);
                //    OldPassengerList.Add(p);
                //}
                //rd2.Close();

                //// chec for each new passenger list -----------------------
                //foreach (Passenger p in book.m_passengerList)
                //{
                //    if (p.m_passangerID > 0)
                //    {
                //        foreach (Passenger op in OldPassengerList)
                //        {
                //            if (p.m_passangerID == op.m_passangerID)
                //            { }
                //        }
                //    }
                //}


                //CHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHECCCCCCKKKKKKKKKKKKKKKKKK
                cmd.Parameters.Clear();
                cmd.CommandText = PassangerSQL.DeletePassangerByBookingIDAndTravelAgentID();
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = book.m_bookingID;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                foreach (Passenger p in book.m_passengerList)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = PassangerSQL.InsertPassanger();
                    cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = p.m_booking.m_bookingID;
                    cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = p.m_passport.m_passportID;
                    cmd.Parameters.Add("m_ticketNo", MySqlDbType.VarChar).Value = p.m_ticketNo;
                    cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.VarChar).Value = p.m_boardingPassCode;
                    cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = p.m_passengerStatus == PassengerStatus.Boarding ? PassengerStatus.ReCheckIn.ToString() : p.m_passengerStatus.ToString();
                    cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = p.m_passport.m_passportNo;
                    cmd.Parameters.Add("m_issueDate", MySqlDbType.Datetime).Value = p.m_passport.m_issueDate;
                    cmd.Parameters.Add("m_expireDate", MySqlDbType.Datetime).Value = p.m_passport.m_expireDate;
                    cmd.Parameters.Add("m_country", MySqlDbType.VarChar).Value = p.m_passport.m_country;
                    cmd.Parameters.Add("m_officeIssue", MySqlDbType.VarChar).Value = p.m_passport.m_officeIssue;
                    cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = p.m_passport.m_name;
                    cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = p.m_passport.m_gender;
                    cmd.Parameters.Add("m_birthPlace", MySqlDbType.VarChar).Value = p.m_passport.m_birthPlace;
                    cmd.Parameters.Add("m_birthDate", MySqlDbType.Datetime).Value = p.m_passport.m_birthDate;
                    cmd.Parameters.Add("m_nationality", MySqlDbType.VarChar).Value = p.m_passport.m_nationality;
                    cmd.Parameters.Add("m_placeOfResidence", MySqlDbType.VarChar).Value = p.m_passport.m_placeOfResidence;
                    cmd.Parameters.Add("m_occupation", MySqlDbType.VarChar).Value = p.m_passport.m_occupation;
                    cmd.Parameters.Add("m_maritalStatus", MySqlDbType.VarChar).Value = p.m_passport.m_maritalStatus;
                    cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = p.m_passport.m_address;
                    cmd.Parameters.Add("m_city", MySqlDbType.VarChar).Value = p.m_passport.m_city;
                    cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = p.m_passport.m_zipcode;
                    cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = p.m_passport.m_remark;
                    cmd.Parameters.Add("m_idnumber", MySqlDbType.VarChar).Value = p.m_passport.m_idnumber;
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                    cmd.ExecuteNonQuery();
                }
                //UPdate bookstock minus
                cmd.CommandText = PassangerSQL.GetPassengerCountByTravelAgent();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                countPassenger = Convert.ToInt32(cmd.ExecuteScalar());
                cmd.Parameters.Clear();
                cmd.CommandText = BookingSQL.UpdateSubstractOneWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = book.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("substract", MySqlDbType.Decimal).Value = countPassenger;
                cmd.ExecuteNonQuery();

            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            } 
        }

        public void InserCheckIn(CheckIn booking)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                //GEt AUTO NUMBER
                bool auto = booking.m_bookingCode.ToLower() == "autonumber";
                MySqlCommand cmd = new MySqlCommand(); 
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                if (auto)
                {
                    cmd = new MySqlCommand(AutoNumberSQL.GetLatestNumberByAgentAndType(), m_dataBaseConectivity.InterfaceSqlConnection);
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                    cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = booking.m_bookingStatus.ToString();
                    int nextnumber = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
                    string nextnumbers = nextnumber.ToString().PadLeft(6, '0');
                    if (booking.m_bookingStatus == CheckInStatus.Booking)
                        booking.m_bookingCode = booking.m_travelAgent.Code + "-B" + nextnumbers;
                    else
                        booking.m_bookingCode = booking.m_travelAgent.Code + "-C" + nextnumbers;
                }
                //Get Last Balance
                cmd.Parameters.Clear();
                if (booking.m_ticketType == TicketType.OneWay)
                    cmd.CommandText = BookingSQL.GetOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.GetTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("substract", MySqlDbType.Decimal).Value = booking.m_bookNumber;
                booking.m_lastBalance = Convert.ToDecimal(cmd.ExecuteScalar());
                //INSERT TA
                cmd.Parameters.Clear();
                cmd.CommandText = BookingSQL.InsertBooking();
                cmd.Parameters.Add("m_bookingCode", MySqlDbType.VarChar).Value = booking.m_bookingCode;
                cmd.Parameters.Add("m_groupName", MySqlDbType.VarChar).Value = booking.m_groupName;
                cmd.Parameters.Add("m_bookNumber", MySqlDbType.Decimal).Value = booking.m_bookNumber;
                cmd.Parameters.Add("m_lastBalance", MySqlDbType.Decimal).Value = booking.m_lastBalance;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = booking.m_trip.m_tripID;
                cmd.Parameters.Add("m_returntrip", MySqlDbType.Int32).Value = booking.m_returntrip == null ? 0 : booking.m_returntrip.m_tripID;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = booking.m_ticketType;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = booking.m_remark;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.VarChar).Value = booking.m_bookingStatus;
                cmd.Parameters.Add("m_returnDate", MySqlDbType.Datetime).Value = booking.m_returnDate;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = booking.m_departureDate;
                cmd.Parameters.Add("m_bookingDate", MySqlDbType.Datetime).Value = booking.m_bookingDate;

                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = booking.m_cretnBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = booking.m_cretnBy;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                 cmd.Parameters.Clear();
                 cmd.CommandText = BookingSQL.GetMaxBookingID();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                booking.m_bookingID = Convert.ToInt64(cmd.ExecuteScalar());
                //INSERT BOOKING HISTORY.................
                cmd.Parameters.Clear();
                cmd.CommandText = BookingSQL.InsertBookingHistory();
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = booking.m_bookingID;
                cmd.Parameters.Add("m_bookingCode", MySqlDbType.VarChar).Value = booking.m_bookingCode;
                cmd.Parameters.Add("m_groupName", MySqlDbType.VarChar).Value = booking.m_groupName;
                cmd.Parameters.Add("m_bookNumber", MySqlDbType.Decimal).Value = booking.m_bookNumber;
                cmd.Parameters.Add("m_lastBalance", MySqlDbType.Decimal).Value = booking.m_lastBalance;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = booking.m_trip.m_tripID;
                cmd.Parameters.Add("m_returntrip", MySqlDbType.Int32).Value = booking.m_returntrip == null ? 0 : booking.m_returntrip.m_tripID;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = booking.m_ticketType;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = booking.m_remark;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.VarChar).Value = booking.m_bookingStatus;
                cmd.Parameters.Add("m_returnDate", MySqlDbType.Datetime).Value = booking.m_returnDate;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = booking.m_departureDate;
                cmd.Parameters.Add("m_bookingDate", MySqlDbType.Datetime).Value = booking.m_bookingDate;
                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = booking.m_cretnBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = booking.m_cretnBy;
                cmd.ExecuteScalar();
                //-------------------------------------------
               
                foreach(Passenger p in booking.m_passengerList)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = PassangerSQL.InsertPassanger();
                    cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = p.m_booking.m_bookingID;
                    cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = p.m_passport.m_passportID;
                    cmd.Parameters.Add("m_ticketNo", MySqlDbType.VarChar).Value = p.m_ticketNo;
                    cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.VarChar).Value = p.m_boardingPassCode;
                    cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = p.m_passengerStatus.ToString();
                    cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = p.m_passport.m_passportNo;
                    cmd.Parameters.Add("m_issueDate", MySqlDbType.Datetime).Value = p.m_passport.m_issueDate;
                    cmd.Parameters.Add("m_expireDate", MySqlDbType.Datetime).Value = p.m_passport.m_expireDate;
                    cmd.Parameters.Add("m_country", MySqlDbType.VarChar).Value = p.m_passport.m_country;
                    cmd.Parameters.Add("m_officeIssue", MySqlDbType.VarChar).Value = p.m_passport.m_officeIssue;
                    cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = p.m_passport.m_name;
                    cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = p.m_passport.m_gender;
                    cmd.Parameters.Add("m_birthPlace", MySqlDbType.VarChar).Value = p.m_passport.m_birthPlace;
                    cmd.Parameters.Add("m_birthDate", MySqlDbType.Datetime).Value = p.m_passport.m_birthDate;
                    cmd.Parameters.Add("m_nationality", MySqlDbType.VarChar).Value = p.m_passport.m_nationality;
                    cmd.Parameters.Add("m_placeOfResidence", MySqlDbType.VarChar).Value = p.m_passport.m_placeOfResidence;
                    cmd.Parameters.Add("m_occupation", MySqlDbType.VarChar).Value = p.m_passport.m_occupation;
                    cmd.Parameters.Add("m_maritalStatus", MySqlDbType.VarChar).Value = p.m_passport.m_maritalStatus;
                    cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = p.m_passport.m_address;
                    cmd.Parameters.Add("m_city", MySqlDbType.VarChar).Value = p.m_passport.m_city;
                    cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = p.m_passport.m_zipcode;
                    cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = p.m_passport.m_remark;
                    cmd.Parameters.Add("m_idnumber", MySqlDbType.VarChar).Value = p.m_passport.m_idnumber;
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    cmd.CommandText = PassangerSQL.GetMaxPassangerID();
                    p.m_passangerID = Convert.ToInt64(cmd.ExecuteScalar());
                    cmd.Parameters.Clear();
                    //INSERT PASSENGER HISTORY.................
                    cmd.CommandText = PassangerSQL.InsertPassangerHistory();
                    cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = p.m_passangerID;
                    cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = p.m_booking.m_bookingID;
                    cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = p.m_passport.m_passportID;
                    cmd.Parameters.Add("m_ticketNo", MySqlDbType.VarChar).Value = p.m_ticketNo;
                    cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.VarChar).Value = p.m_boardingPassCode;
                    cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = p.m_passengerStatus.ToString();
                    cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = p.m_passport.m_passportNo;
                    cmd.Parameters.Add("m_issueDate", MySqlDbType.Datetime).Value = p.m_passport.m_issueDate;
                    cmd.Parameters.Add("m_expireDate", MySqlDbType.Datetime).Value = p.m_passport.m_expireDate;
                    cmd.Parameters.Add("m_country", MySqlDbType.VarChar).Value = p.m_passport.m_country;
                    cmd.Parameters.Add("m_officeIssue", MySqlDbType.VarChar).Value = p.m_passport.m_officeIssue;
                    cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = p.m_passport.m_name;
                    cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = p.m_passport.m_gender;
                    cmd.Parameters.Add("m_birthPlace", MySqlDbType.VarChar).Value = p.m_passport.m_birthPlace;
                    cmd.Parameters.Add("m_birthDate", MySqlDbType.Datetime).Value = p.m_passport.m_birthDate;
                    cmd.Parameters.Add("m_nationality", MySqlDbType.VarChar).Value = p.m_passport.m_nationality;
                    cmd.Parameters.Add("m_placeOfResidence", MySqlDbType.VarChar).Value = p.m_passport.m_placeOfResidence;
                    cmd.Parameters.Add("m_occupation", MySqlDbType.VarChar).Value = p.m_passport.m_occupation;
                    cmd.Parameters.Add("m_maritalStatus", MySqlDbType.VarChar).Value = p.m_passport.m_maritalStatus;
                    cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = p.m_passport.m_address;
                    cmd.Parameters.Add("m_city", MySqlDbType.VarChar).Value = p.m_passport.m_city;
                    cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = p.m_passport.m_zipcode;
                    cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = p.m_passport.m_remark;
                    cmd.Parameters.Add("m_idnumber", MySqlDbType.VarChar).Value = p.m_passport.m_idnumber;
                    cmd.ExecuteNonQuery();

                    //------------------------------------------------
                }
                //UPDATE NEXT NUMBER 
                if (auto)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = AutoNumberSQL.UpdateLatestAutoNumber();
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                    cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = booking.m_bookingStatus.ToString();
                    cmd.ExecuteNonQuery();
                }
                //UPdate bookstock minus
                cmd.Parameters.Clear();
                if (booking.m_bookingStatus == CheckInStatus.Booking)
                {
                    if (booking.m_ticketType == TicketType.OneWay)
                        cmd.CommandText = BookingSQL.UpdateSubstractOneWayBookStock();
                    else
                        cmd.CommandText = BookingSQL.UpdateSubstractTwoWayBookStock();
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                    cmd.Parameters.Add("substract", MySqlDbType.Decimal).Value = booking.m_bookNumber;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList LoadAllCheckInByTravelAgent(long p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.GetAllBookingByTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value =p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    CheckIn user = new CheckIn(rd);
                    result.Add(user);

                    MySqlCommand cmd2 = new MySqlCommand(PassangerSQL.GetAllPassangerByBookingID(), m_dataBaseConectivity.InterfaceSqlConnection_b);
                    cmd2.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = user.m_bookingID;
                    //LogService.WriteLogExecuteSQLCommand(cmd);
                    MySqlDataReader rd2 = cmd2.ExecuteReader();
                    while (rd2.Read())
                    {
                        Passenger pss = new Passenger(rd2);
                        user.m_passengerList.Add(pss);
                    }
                    rd2.Close();

                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        //public decimal GetAvailableBook(long agentID, TicketType ticketType)
        //{
        //    decimal result = 0;
        //    decimal totalPurchase= 0;
        //    decimal totalBook= 0;
        //    try
        //    {

        //        m_dataBaseConectivity.InterfaceSqlConnection.Open();
        //        MySqlCommand cmd = new MySqlCommand(TicketPurchaseSQL.GetTotalPurchaseByAgentID(), m_dataBaseConectivity.InterfaceSqlConnection);
        //        cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = agentID;
        //        cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = ticketType.ToString();
        //        //LogService.WriteLogExecuteSQLCommand(cmd);
        //        if(!(cmd.ExecuteScalar() is DBNull))
        //            totalPurchase = Convert.ToDecimal(cmd.ExecuteScalar());
        //        cmd.Parameters.Clear();
        //        cmd.CommandText = BookingSQL.GetTotalBookingByTravelAgent();
        //        //LogService.WriteLogExecuteSQLCommand(cmd);
        //        cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = agentID;
        //        if (!(cmd.ExecuteScalar() is DBNull))
        //            totalBook = Convert.ToDecimal(cmd.ExecuteScalar());
        //        result = totalPurchase - totalBook;
        //    }
        //    catch (Exception x)
        //    {
        //        LogService.WriteLogException(x);
        //        throw x;
        //    }
        //    finally
        //    {
        //        m_dataBaseConectivity.InterfaceSqlConnection.Close();
        //    }
        //    return result;
        //}
        public decimal GetAvailableBook(long agentID, TicketType ticketType)
        {
            decimal result =0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                if (ticketType == TicketType.OneWay)
                    cmd.CommandText = BookingSQL.GetOneWayBookStock();
                else
                    cmd.CommandText = BookingSQL.GetTwoWayBookStock();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = agentID;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = ticketType.ToString();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                if (!(cmd.ExecuteScalar() is DBNull))
                    result = Convert.ToDecimal(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void UpdatePassangerStatus(Passenger passenger, PassengerStatus status)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.UpdatePassangerStatus(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = status;
                cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = passenger.m_passangerID;
                cmd.ExecuteNonQuery();
                passenger.m_passengerStatus = status;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdatePassangerStatusAndBoardingPass(Passenger passenger, PassengerStatus status)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.UpdatePassangerStatusAndBoardingPass(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = status;
                cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = passenger.m_passangerID;
                cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.String).Value = passenger.m_boardingPassCode;
                cmd.ExecuteNonQuery();
                passenger.m_passengerStatus = status;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        
        public void UpdateCheckInStatus(CheckIn booking, CheckInStatus status)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.UpdateBookingStatus(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.VarChar).Value = status;
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = booking.m_bookingID;
                cmd.ExecuteNonQuery();
                booking.m_bookingStatus = status;
                //INSERT BOOKING HISTORY.................
                cmd.Parameters.Clear();
                cmd.CommandText = BookingSQL.InsertBookingHistory();
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = booking.m_bookingID;
                cmd.Parameters.Add("m_bookingCode", MySqlDbType.VarChar).Value = booking.m_bookingCode;
                cmd.Parameters.Add("m_groupName", MySqlDbType.VarChar).Value = booking.m_groupName;
                cmd.Parameters.Add("m_bookNumber", MySqlDbType.Decimal).Value = booking.m_bookNumber;
                cmd.Parameters.Add("m_lastBalance", MySqlDbType.Decimal).Value = booking.m_lastBalance;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = booking.m_trip.m_tripID;
               // cmd.Parameters.Add("m_returntrip", MySqlDbType.Int32).Value = booking.m_returntrip.m_tripID;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value = booking.m_ticketType;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = booking.m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = booking.m_remark;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.VarChar).Value = booking.m_bookingStatus;
                cmd.Parameters.Add("m_returnDate", MySqlDbType.Datetime).Value = booking.m_returnDate;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = booking.m_departureDate;
                cmd.Parameters.Add("m_bookingDate", MySqlDbType.Datetime).Value = booking.m_bookingDate;
                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = booking.m_cretnBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = booking.m_cretnBy;
                cmd.ExecuteScalar();
                //-------------------------------------------
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public DataSet ReportItineraryBookingByID(long id)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.ReportItineraryBookingByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public DataSet ReportItineraryBookingPassengerByID(long id)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.ReportItineraryBookingPassengerByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public DataSet ReportKIMSCardPassengerByID(long id)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.ReportKIMSPassengerByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public DataSet ReportBooking(DateTime fromdate, DateTime todate, long taId, long seaId, long vessId)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.ReportBooking(), m_dataBaseConectivity.InterfaceSqlConnection);
                if (taId > 0)
                {
                    cmd.CommandText += " and ta.m_travelAgentId = ?m_travelAgentId ";
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = taId;
                }
                if (seaId > 0)
                {
                    cmd.CommandText += " and s.m_seaPortID  = ?m_seaPortID ";
                    cmd.Parameters.Add("m_seaPortID", MySqlDbType.Int32).Value = seaId;
                }
                if (vessId > 0)
                {
                    cmd.CommandText += " and v.m_vesselID  = ?m_vesselID ";
                    cmd.Parameters.Add("m_vesselID", MySqlDbType.Int32).Value = vessId;
                }
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.CommandText += " order by t.m_departureTime";
                cmd.Parameters.Add("fromdate", MySqlDbType.Datetime).Value = fromdate;
                cmd.Parameters.Add("todate", MySqlDbType.Datetime).Value = todate;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public DataSet ReportTicket(DateTime fromdate, DateTime todate, long taId, long seaId, long vessId, string ticketNo)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.ReportTicket(), m_dataBaseConectivity.InterfaceSqlConnection);
                if (taId > 0)
                {
                    cmd.CommandText += " and ta.m_travelAgentId = ?m_travelAgentId ";
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = taId;
                }
                if (seaId > 0)
                {
                    cmd.CommandText += " and s.m_seaPortID  = ?m_seaPortID ";
                    cmd.Parameters.Add("m_seaPortID", MySqlDbType.Int32).Value = seaId;
                }
                if (vessId > 0)
                {
                    cmd.CommandText += " and v.m_vesselID  = ?m_vesselID ";
                    cmd.Parameters.Add("m_vesselID", MySqlDbType.Int32).Value = vessId;
                }
                if (ticketNo.Trim() != string.Empty)
                {
                    cmd.CommandText += " and p.m_ticketNo like '%"+ticketNo+"%' ";
                } 
                cmd.CommandText += " order by t.m_departureTime";
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.Parameters.Add("fromdate", MySqlDbType.Datetime).Value = fromdate;
                cmd.Parameters.Add("todate", MySqlDbType.Datetime).Value = todate;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }
        public DataSet ReportPassenger(string paxstatus, DateTime fromdate, DateTime todate, long taId, long seaId, long vessId)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.ReportTicket(), m_dataBaseConectivity.InterfaceSqlConnection);
                if (taId > 0)
                {
                    cmd.CommandText += " and ta.m_travelAgentId = ?m_travelAgentId ";
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = taId;
                }
                if (seaId > 0)
                {
                    cmd.CommandText += " and s.m_seaPortID  = ?m_seaPortID ";
                    cmd.Parameters.Add("m_seaPortID", MySqlDbType.Int32).Value = seaId;
                }
                if (vessId > 0)
                {
                    cmd.CommandText += " and v.m_vesselID  = ?m_vesselID ";
                    cmd.Parameters.Add("m_vesselID", MySqlDbType.Int32).Value = vessId;
                }
                if (paxstatus.Trim() != "ALL")
                {
                    cmd.CommandText += " and p.m_passengerStatus = ?m_passengerStatus ";
                    cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = paxstatus;
                }               
                cmd.CommandText += " order by t.m_departureTime";
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.Parameters.Add("fromdate", MySqlDbType.Datetime).Value = fromdate;
                cmd.Parameters.Add("todate", MySqlDbType.Datetime).Value = todate;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }
        public Passenger GetPassengerByBoardingPass(string boardingpass)
        {
            Passenger result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByBoardingPass(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.String).Value = boardingpass;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Passenger(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public Passenger GetPassengerByBoardingPass(string boardingpass, Trip trip)
        {
            Passenger result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByBoardingPassAndTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.String).Value = boardingpass;
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Passenger(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public Passenger GetPassengerByBoardingPass(string boardingpass, TravelAgent travelAgent)
        {
            Passenger result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByBoardingPassAndTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.String).Value = boardingpass;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Passenger(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public Passenger GetPassengerByBoardingPass(string boardingpass, TravelAgent travelAgent, Trip trip)
        {
            Passenger result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByBoardingPassAndTravelAgentAndTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.String).Value = boardingpass;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Passenger(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }   
        public IList GetPassengerByStatusAndTrip(PassengerStatus status, Trip trip)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByStatusAndTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.String).Value = status.ToString();
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Passenger(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public int GetPassengerCountByStatusAndTrip(PassengerStatus status, Trip trip)
        {
            int result = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerCountByStatusAndTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.String).Value = status.ToString();
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                result = Convert.ToInt16(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList GetPassengerByStatusAndTrip(PassengerStatus status, Trip trip, TravelAgent travelAgent)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByStatusAndTripAndTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.String).Value = status.ToString();
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Passenger(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList GetPassengerByBookingIDAndTravelAgent(CheckIn checkIn, TravelAgent travelAgent)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetAllPassangerByBookingIDAndTravelAgentID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = checkIn.m_bookingID;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Passenger(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList GetPassengerByStatusAndTripAndCheckInstatus(PassengerStatus status, Trip trip, CheckInStatus ch)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByStatusAndTripAndCheckInStatus(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.String).Value = status.ToString();
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.String).Value = ch.ToString();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Passenger(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList GetPassengerByStatusAndTripAndCheckInstatus(PassengerStatus status, Trip trip, CheckInStatus ch, TravelAgent travelAgent)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByStatusAndTripAndCheckInStatusAndTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.String).Value = status.ToString();
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.String).Value = ch.ToString();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Passenger(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public int GetLatestBoardingPassNumber(Trip p)
        {
            int result = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                //GEt AUTO NUMBER
                MySqlCommand cmd = new MySqlCommand(AutoNumberSQL.GetLatestNumberByAgentAndTypeAndTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = 0;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "BoardingPassCode";
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = p.m_tripID;
                result = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
                //Update
                cmd.CommandText = AutoNumberSQL.UpdateLatestAutoNumberTrip();
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public DataSet ReportManifestByTripID(Trip trip, User user, PassengerStatus ps)
        {
            DataSet result = new DataSet();
            DataTable table1 = result.Tables.Add("Table");
            DataTable table2 = result.Tables.Add("Header");
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.ReportManifest(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = trip.m_tripID;
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.String).Value = ps.ToString();
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(table1);
                cmd.CommandText = BookingSQL.ReportManifestHeader();
                cmd.Parameters.Clear();
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = trip.m_tripID;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.String).Value = user.TravelAgent.TravelAgentID;
                MySqlDataAdapter da2 = new MySqlDataAdapter(cmd);
                da2.Fill(table2);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public IList GetBookingByPassengerPassport(string passport, Trip trip)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByPassportAndTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passportNo", MySqlDbType.String).Value = passport.Trim();
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Passenger(rd));
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public Passenger GetPassengerByTripAndPassport(string passport, Trip trip)
        {
            Passenger result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.GetPassengerByPassportAndTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passportNo", MySqlDbType.String).Value = passport.Trim();
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Passenger(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void AddPassengerCheckIn(Passenger p, TravelAgent travelAgent)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.InsertPassanger(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = p.m_booking.m_bookingID;
                cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = p.m_passport.m_passportID;
                cmd.Parameters.Add("m_ticketNo", MySqlDbType.VarChar).Value = p.m_ticketNo;
                cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.VarChar).Value = p.m_boardingPassCode;
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = p.m_passengerStatus == PassengerStatus.Boarding ? PassengerStatus.ReCheckIn.ToString() : p.m_passengerStatus.ToString();
                cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = p.m_passport.m_passportNo;
                cmd.Parameters.Add("m_issueDate", MySqlDbType.Datetime).Value = p.m_passport.m_issueDate;
                cmd.Parameters.Add("m_expireDate", MySqlDbType.Datetime).Value = p.m_passport.m_expireDate;
                cmd.Parameters.Add("m_country", MySqlDbType.VarChar).Value = p.m_passport.m_country;
                cmd.Parameters.Add("m_officeIssue", MySqlDbType.VarChar).Value = p.m_passport.m_officeIssue;
                cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = p.m_passport.m_name;
                cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = p.m_passport.m_gender;
                cmd.Parameters.Add("m_birthPlace", MySqlDbType.VarChar).Value = p.m_passport.m_birthPlace;
                cmd.Parameters.Add("m_birthDate", MySqlDbType.Datetime).Value = p.m_passport.m_birthDate;
                cmd.Parameters.Add("m_nationality", MySqlDbType.VarChar).Value = p.m_passport.m_nationality;
                cmd.Parameters.Add("m_placeOfResidence", MySqlDbType.VarChar).Value = p.m_passport.m_placeOfResidence;
                cmd.Parameters.Add("m_occupation", MySqlDbType.VarChar).Value = p.m_passport.m_occupation;
                cmd.Parameters.Add("m_maritalStatus", MySqlDbType.VarChar).Value = p.m_passport.m_maritalStatus;
                cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = p.m_passport.m_address;
                cmd.Parameters.Add("m_city", MySqlDbType.VarChar).Value = p.m_passport.m_city;
                cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = p.m_passport.m_zipcode;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = p.m_remark; ;// p.m_passport.m_remark; 19112013 -- use as usercode
                cmd.Parameters.Add("m_idnumber", MySqlDbType.VarChar).Value = p.m_passport.m_idnumber;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                cmd.ExecuteNonQuery();
                cmd.CommandText = PassangerSQL.GetMaxPassangerID();
                p.m_passangerID = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void RemovePassengerCheckIn(Passenger p)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.DeletePassangerByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = p.m_passangerID;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public CheckIn SearchCheckInByTrip(Trip trip)
        {
            CheckIn result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SearchCheckInByTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = trip.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new CheckIn(rd);
                }
                rd.Close();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void UpdatePassanger(Passenger p, TravelAgent travelAgent)
        {
           try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.UpdatePassanger(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = p.m_booking.m_bookingID;
                cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = p.m_passport.m_passportID;
                cmd.Parameters.Add("m_ticketNo", MySqlDbType.VarChar).Value = p.m_ticketNo;
                cmd.Parameters.Add("m_boardingPassCode", MySqlDbType.VarChar).Value = p.m_boardingPassCode;
                cmd.Parameters.Add("m_passengerStatus", MySqlDbType.VarChar).Value = p.m_passengerStatus.ToString();
                cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = p.m_passport.m_passportNo;
                cmd.Parameters.Add("m_issueDate", MySqlDbType.Datetime).Value = p.m_passport.m_issueDate;
                cmd.Parameters.Add("m_expireDate", MySqlDbType.Datetime).Value = p.m_passport.m_expireDate;
                cmd.Parameters.Add("m_country", MySqlDbType.VarChar).Value = p.m_passport.m_country;
                cmd.Parameters.Add("m_officeIssue", MySqlDbType.VarChar).Value = p.m_passport.m_officeIssue;
                cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = p.m_passport.m_name;
                cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = p.m_passport.m_gender;
                cmd.Parameters.Add("m_birthPlace", MySqlDbType.VarChar).Value = p.m_passport.m_birthPlace;
                cmd.Parameters.Add("m_birthDate", MySqlDbType.Datetime).Value = p.m_passport.m_birthDate;
                cmd.Parameters.Add("m_nationality", MySqlDbType.VarChar).Value = p.m_passport.m_nationality;
                cmd.Parameters.Add("m_placeOfResidence", MySqlDbType.VarChar).Value = p.m_passport.m_placeOfResidence;
                cmd.Parameters.Add("m_occupation", MySqlDbType.VarChar).Value = p.m_passport.m_occupation;
                cmd.Parameters.Add("m_maritalStatus", MySqlDbType.VarChar).Value = p.m_passport.m_maritalStatus;
                cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = p.m_passport.m_address;
                cmd.Parameters.Add("m_city", MySqlDbType.VarChar).Value = p.m_passport.m_city;
                cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = p.m_passport.m_zipcode;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = p.m_remark;
                cmd.Parameters.Add("m_idnumber", MySqlDbType.VarChar).Value = p.m_passport.m_idnumber;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = travelAgent.TravelAgentID;
                cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = p.m_passangerID;
                cmd.ExecuteNonQuery();
            }
           catch (Exception x)
           {
               LogService.WriteLogException(x);
               throw x;
           }
           finally
           {
               m_dataBaseConectivity.InterfaceSqlConnection.Close();
           }
        }

        public void TransferPassenger(Passenger p, Trip newTrip)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(BookingSQL.SelectNewBooking(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_tripid", MySqlDbType.Int32).Value = newTrip.m_tripID;
                MySqlDataReader rd = cmd.ExecuteReader();
                CheckIn checkIn = null;
                while (rd.Read())
                    checkIn = new CheckIn(rd);
                rd.Close();
                if (checkIn == null)
                    throw new Exception("New trip can not be found");
                cmd = new MySqlCommand(BookingSQL.UpdateTripByPassengerID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_bookingID", MySqlDbType.Int32).Value = checkIn.m_bookingID;
                cmd.Parameters.Add("m_passangerID", MySqlDbType.Int32).Value = p.m_passangerID;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public bool IsValidTicket(string ticketNumber)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassangerSQL.FindTicket(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_ticketNo", MySqlDbType.VarChar).Value = ticketNumber;
                int total = Convert.ToInt32(cmd.ExecuteScalar());
                return total == 0;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
